package com.data.dao;

import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeShift;


@Repository("chargeShiftDao")
public class ChargeShiftDaoImpl extends TemplateDaoImpl<ChargeShift, Integer>implements ChargeShiftDao{
	/**
	 * 根据生产线或者设备id，班次id，归属时间查询班次电费记录
	 * @param id
	 * @param shiftId
	 * @param belongDate
	 * @param chargeName
	 * @param  isLine
	 * @return
	 */
	@Override
	public ChargeShift getChargeShiftByIdAndDate(int id, int shiftId,String chargeName,String belongDate,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeShift where  ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and shiftId=:shiftId and chargeName=:chargeName  and CONVERT(VARCHAR(10),belongDate,120)=:belongDate");
//		String sql="select * from chargeShift where equId=:equId and shiftId=:shiftId and chargeName=:chargeName  and CONVERT(VARCHAR(10),belongDate,120)=:belongDate";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("shiftId", shiftId);
		params.put("chargeName", chargeName);
		params.put("belongDate", belongDate);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeShift.class));	
	}    
	
	/**
	 * 查询某天某班次总电度和总电费
	 * @param id
	 * @param shiftId
	 * @param belongDate
	 * @return
	 */
	@Override
	public ChargeShift getShiftElectricAndChargeByDate(int id, int shiftId, String belongDate,String isLine) {
		//COALESCE函数，relativeComp为null时返回0
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp ,COALESCE(SUM(charge),0) charge  from chargeShift where  ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
//		String sql="select  COALESCE(SUM(relativeComp),0)  from chargeShift where lineId=:lineId   and shiftId=:shiftId   and CONVERT(VARCHAR(10),belongDate,120)=:belongDate";
		sb.append(" and shiftId=:shiftId   and CONVERT(VARCHAR(10),belongDate,120)=:belongDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("shiftId", shiftId);
		params.put("belongDate", belongDate);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeShift.class));	
	}
	/**
	 * 根据开始日期，结束日期查询班次电费电度
	 * @param id
	 * @param shiftId
	 * @param startDate
	 * @param endDate
	 * @param isLine
	 * @return
	 */
	@Override
	public ChargeShift getShiftElectricAndChargeByAreaDate(int id, int shiftId, String startDate, String endDate, String isLine) {
		//COALESCE函数，relativeComp为null时返回0
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp ,COALESCE(SUM(charge),0) charge  from chargeShift where  ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
//		String sql="select  COALESCE(SUM(relativeComp),0)  from chargeShift where lineId=:lineId   and shiftId=:shiftId   and CONVERT(VARCHAR(10),belongDate,120)=:belongDate";
		sb.append(" and shiftId=:shiftId   and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("shiftId", shiftId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeShift.class));	
	}
}
